﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Lamex.Base;
using System.IO;
using DevExpress.ClipboardSource.SpreadsheetML;

namespace Lamex.Library.UI
{
    public partial class FrmImport : DevExpress.XtraEditors.XtraForm
    {
        string tableName = "";
        DataTable dtcol = null;
        DataTable dtDatasource = null;
        string dataName = "";
        public FrmImport(string TableName, DataTable dtDatasource,string dataName)
        {
            tableName = TableName;
            this.dataName = dataName;
            this.dtDatasource = dtDatasource;
            InitializeComponent();
            if (dataName.ToLower() == "LamexMESManager".ToLower())
                dtcol = BaseBuildSql.GetSysColunmNameList(tableName);
            else
                dtcol = GetSysColunmNameList2(tableName,dataName);
            gcMain.DataSource = dtcol;
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            if (DialogResult.OK == dlg.ShowDialog())
            {
                using (DevExpress.Utils.WaitDialogForm wdf = new DevExpress.Utils.WaitDialogForm("请稍等", "正在导入数据...", new Size(400, 80)))
                {
                    try
                    {
                        string path = dlg.FileName;
                        DataTable dt = Lamex.Common.ExecelHelper.ExcelConvertDataTable(0, true, path);
                        foreach (DataColumn col in dt.Columns)
                        {
                            foreach (DataRow dr in dtDatasource.Rows)
                            {
                                if (dr["CTRL_CN_NAME"].ToString() == col.ColumnName)
                                {
                                    col.ColumnName = dr["WRITE_FIELD_NAME"].ToString();
                                    
                                }
                            }
                        }
                        bool HasBitFlag = false;
                        foreach (DataRow dr in dtDatasource.Rows)
                        {
                            if (dr["CTRL_VALUE_TYPE"].ToString().ToLower() == "bit")
                            {
                                HasBitFlag = true;
                            }
                        }


                        //convert datasource field type ,just check and  change bit type .be caution
                        if (HasBitFlag == true)
                        {
                            DataTable dtcopy = dt.Clone();
                            foreach (DataColumn col in dtcopy.Columns)
                            {
                                foreach (DataRow dr in dtDatasource.Rows)
                                {
                                    if (dr["WRITE_FIELD_NAME"].ToString() == col.ColumnName)
                                    {
                                        //col.ColumnName = dr["WRITE_FIELD_NAME"].ToString();
                                        if (dr["CTRL_VALUE_TYPE"].ToString().ToLower() == "string")
                                            col.DataType = typeof(string);
                                        if (dr["CTRL_VALUE_TYPE"].ToString().ToLower() == "int")
                                            col.DataType = typeof(int);
                                        if (dr["CTRL_VALUE_TYPE"].ToString().ToLower() == "bit")
                                            col.DataType = typeof(bool);
                                        if (dr["CTRL_VALUE_TYPE"].ToString().ToLower().Contains("decimal"))
                                            col.DataType = typeof(decimal);
                                    }
                                }
                            }
                            foreach (DataRow dr in dt.Rows)
                            {
                                DataRow drrow = dtcopy.NewRow();
                                foreach (DataColumn dc in dtcopy.Columns)
                                {
                                    if (dc.DataType == typeof(bool))
                                    {
                                        if (dr[dc.ColumnName].ToString() == "1")
                                            drrow[dc.ColumnName] = true;
                                        else
                                            drrow[dc.ColumnName] = false;
                                    }
                                    else
                                        drrow[dc.ColumnName] = dr[dc.ColumnName];
                                }
                                // drrow.ItemArray= dr.ItemArray;
                                dtcopy.Rows.Add(drrow);


                            }

                            dt = dtcopy;
                        }
                        foreach (DataColumn col in dt.Columns)
                        {

                            if (col.ColumnName.ToUpper().Contains("UPDATE_USER")||col.ColumnName.ToUpper().Contains("ss") || col.ColumnName.ToUpper().Contains("ss"))
                            {
                                foreach (DataRow dr in dt.Rows)
                                {
                                    dr[col.ColumnName] = Lamex.Common.Loginer.CurrentUser.AccountName;
                                }
                            }
                            if (col.ColumnName.ToUpper().Contains("UPDATE_TIME") || col.ColumnName.ToUpper().Contains("ss") || col.ColumnName.ToUpper().Contains("ss"))
                            {
                                foreach (DataRow dr in dt.Rows)
                                {
                                    dr[col.ColumnName] = DateTime.Now;
                                }
                            }
                        }
                        //dtcol = BaseBuildSql.GetSysColunmNameList(tableName);
                        DataTable dtSave = new DataTable();
                        dt.TableName = tableName;
                        string sql = "insert into " + dataName+".dbo." +tableName + "( ";
                        string sql2 = "select ";
                        string field = "";
                        string field2 = "";
                        List<string> removeList = new List<string>();
                        List<string> keyLst = new List<string>() ;
                        List<string> keyLstStr = new List<string>();
                        bool IsKeyCheck = false;
                        foreach (DataRow dr in dtcol.Rows)
                        {
                            dtcol.TableName = dr["table_name"].ToString();
                            if (dr["keyname"].ToString().ToLower() == "key")
                            {
                                DataTable IsCheckAuto = BaseBuildSql.GetDataTableBySql(@"SELECT * FROM INFORMATION_SCHEMA.columns
                                WHERE TABLE_NAME = '" + dataName + ".dbo." + tableName + @"' AND COLUMNPROPERTY(OBJECT_ID('" + dataName + ".dbo." + tableName + @"'), COLUMN_NAME, 'IsIdentity') = 1
                                and COLUMN_NAME = '" + dr["keyname"].ToString().ToLower() + "'");

                                //DataTable IsBindList = BaseBuildSql.GetDataTableBySql(@"select b.text as IsBildList 
                                //                                                    from syscolumns a left join syscomments b on a.cdefault = b.id 
                                //                                                    where a.id = object_id('" + tableName + @"') and a.name = '" + dr["keyname"].ToString().ToLower() + @"'
                                //                                                    and ISNULL(b.text,'')!=''");
                                if (IsCheckAuto.Rows.Count != 0)
                                    keyLst.Add(dr["column_name"].ToString().ToLower());
                                //if(IsBindList.Rows.Count!=0)
                                //keyLst.Add(dr["column_name"].ToString().ToLower());
                                keyLstStr.Add(dr["column_name"].ToString().ToLower());
                                IsKeyCheck = true;
                            }
                            int m = 0;
                            foreach (DataColumn d in dt.Columns)
                            {

                                if (dr["column_name"].ToString() == d.ColumnName)
                                {
                                    if (dr["column_type"].ToString().Contains("image"))
                                    {
                                        removeList.Add(dt.Columns[d.ColumnName].ColumnName);

                                    }
                                    else
                                    {
                                        string Ischeck = "";
                                        string str = keyLst.Where(p => p.Contains(dr["column_name"].ToString().ToLower())).FirstOrDefault();
                                        if (!string.IsNullOrEmpty(str))
                                        {
                                            DataTable IsCheckAuto = BaseBuildSql.GetDataTableBySql(@"SELECT * FROM INFORMATION_SCHEMA.columns
                                                                            WHERE TABLE_NAME = '" +dataName+"dbo."+ tableName + @"' AND COLUMNPROPERTY(OBJECT_ID('" +dataName+".dbo." +tableName + @"'), COLUMN_NAME, 'IsIdentity') = 1
                                                                            and COLUMN_NAME = '" + dr["keyname"].ToString().ToLower() + "'");
                                            if (IsCheckAuto.Rows.Count != 0)
                                                Ischeck = IsCheckAuto.Rows[0][0].ToString();
                                        }
                                        
                                        

                                        m = 1;
                                        if (string.IsNullOrEmpty(Ischeck))
                                        {
                                            field += dr["column_name"].ToString() + ",";
                                            field2 += dr["column_name"].ToString() + ",";
                                            dt.Columns[d.ColumnName].ColumnName = dr["column_name"].ToString();
                                        }
                                        else
                                        {
                                            removeList.Add(dr["column_name"].ToString());
                                        }
                                    }

                                }
                            }
                            //if (m==0)
                            //{
                            //    field += dr["column_name"].ToString() + ",";
                            //    field2 += dr["column_name"].ToString() + ",";
                            //    dt.Columns.Add(dr["column_name"].ToString(), Lamex.Common.DTHelper.dbTypeConvertType(dr["column_type"].ToString()));
                            //} 
                            //}

                        }
                        field = field.Trim(',');
                        field2 = field2.Trim(',');
                        sql += field;
                        sql2 += field2;

                        sql += " ) ";
                        sql2 += " from  " + dataName + ".dbo.#" + tableName + "";
                        sql = sql + sql2;
                        if (string.IsNullOrEmpty(field))
                        {
                            XtraMessageBox.Show("【导入表格列名称不对】");
                            return;
                        }
                        foreach (string s in removeList)
                        {
                            dt.Columns.Remove(dt.Columns[s]);
                        }
                        int iReRow = 0;
                        int iInsRow = 0;
                        int iTotal = 0;
                        DataTable dtInset = dt.Clone() ;
                        DataTable dtSuccess = dt.Clone();
                        if (IsKeyCheck)
                        {
                            dtInset.Columns.Add("Status");
                            
                            foreach (DataRow dr in dt.Rows)
                            {
                                
                                string strWhere = "";
                                DataRow drNew = dtInset.NewRow();
                                DataRow drInsertRow = dtSuccess.NewRow();
                                foreach (string s in keyLstStr)
                                {
                                    strWhere += "and " + s + " = '" + dr[s] + "'";
                                }
                                DataTable dtest = BaseBuildSql.GetDataTableBySql("Select * from " + dataName + ".dbo." + tableName + " where 1=1 " + strWhere);
                                if (dtest.Rows.Count > 0)
                                {
                                    iReRow++;
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                        drNew[col.ColumnName] = dr[col.ColumnName];
                                    }
                                    drNew["Status"] = "数据已存在";
                                    
                                }
                                else
                                {
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                        drNew[col.ColumnName] = dr[col.ColumnName];
                                    }
                                    drNew["Status"] = "成功";
                                    drInsertRow.ItemArray = dr.ItemArray;
                                    dtSuccess.Rows.Add(drInsertRow);
                                }
                                
                                dtInset.Rows.Add(drNew);
                                
                                
                            }

                            iInsRow = dtInset.Rows.Count;
                            DataSet ds = new DataSet();
                            if (dtSuccess.Rows.Count != 0)
                            {

                                ds.Tables.Add(dtSuccess);
                                string errMsg = "";
                                if (BaseBuildSql.ExecuteInsertBulkData("select  " + field + "  into " + dataName + ".dbo.#" + tableName + " from  " + dataName+".dbo."+tableName + " where 1!=1", ds, sql, ref errMsg))
                                {
                                    XtraMessageBox.Show("成功导入");
                                    this.Close();
                                }
                                else
                                {
                                    XtraMessageBox.Show(errMsg);
                                }
                            }
                        }
                        else
                        {
                            foreach (DataRow dr in dt.Rows)
                            {

                                string strWhere = "";
                                DataRow drNew = dtInset.NewRow();
                                DataRow drInsertRow = dtSuccess.NewRow();
                                foreach (string s in keyLst)
                                {
                                    strWhere += "and " + s + " = '" + dr[s] + "'";
                                }
                                DataTable dtest = BaseBuildSql.GetDataTableBySql("Select * from " + dataName + ".dbo." + tableName + " where 1=1 " + strWhere);
                                if (dtest.Rows.Count > 0)
                                {
                                    iReRow++;
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                        drNew[col.ColumnName] = dr[col.ColumnName];
                                    }
                                    drNew["Status"] = "数据已存在";

                                }
                                else
                                {
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                        drNew[col.ColumnName] = dr[col.ColumnName];
                                    }
                                    
                                    drNew["Status"] = "成功";
                                    drInsertRow.ItemArray = dr.ItemArray;
                                    dtSuccess.Rows.Add(drInsertRow);

                                }

                                dtInset.Rows.Add(drNew);
                            }
                            DataSet ds = new DataSet();
                            if (dtSuccess.Rows.Count != 0)
                            {
                                ds.Tables.Add(dtSuccess);
                                string errMsg = "";
                                if (BaseBuildSql.ExecuteInsertBulkData("select  " + field + "  into " + dataName + ".dbo.#" + tableName + " from  " +dataName+".dbo."+ tableName + " where 1!=1", ds, sql, ref errMsg))
                                {
                                    XtraMessageBox.Show("成功导入");
                                    this.Close();
                                }
                                else
                                {
                                    XtraMessageBox.Show(errMsg);
                                }
                            }
                        }
                        string paths = @"D:\MESTemp\";
                        if (!Directory.Exists(paths))
                        {
                            Directory.CreateDirectory(paths);
                        }
                       
                        paths = paths + "MES_TEMP_" + DateTime.Now.ToString("yyyyMMdd_HHmmss")+ ".xlsx";

                        Lamex.Common.ExecelHelper.ExportDataTableToExcelHeader(dtInset, paths);
                        if (iReRow > 0)
                        {
                            if (DevExpress.XtraEditors.XtraMessageBox.Show("成功："+ dtSuccess.Rows.Count+"条；失败："+iReRow+"条\r\n"+ "是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                                System.Diagnostics.Process.Start(paths);
                        }

                    }
                    catch( Exception ex)
                    {
                        XtraMessageBox.Show(ex.Message);
                    }
                }
            }
        }

        private DataTable GetSysColunmNameList2(string tableName, string dataName)
        {
            string sql = @"SELECT  
                        d.name as table_name,  
                        a.name column_name,
                        case when exists(SELECT 1 FROM {0}.dbo.sysobjects where xtype='PK' and name in (
                        SELECT name FROM {1}.dbo.sysindexes WHERE indid in(
                        SELECT indid FROM {2}.dbo.sysindexkeys WHERE id = a.id AND colid=a.colid 
                        ))) then 'key' else '' end as keyname, 
                        b.name column_type,
                        isnull(g.[value],'') column_remark
                        FROM {3}.dbo.syscolumns a 
                        left join {4}.dbo.systypes b on a.xtype=b.xusertype 
                        inner join {5}.dbo.sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
                        left join {6}.dbo.syscomments e on a.cdefault=e.id 
                        left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
                        left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 
                        where d.name='MT_Resource_Worker_List' order by a.id,a.colorder ";
            sql = string.Format(sql, dataName, dataName, dataName, dataName, dataName, dataName, dataName);
            return Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);

        }
    }
}